package com.cedar.util; 

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.LinkedHashMap;

/**  
 * @Author Bruce 
 * @Date 2012-9-30
 * @Comments For Mysql DB
 */
public class DBUtil {
	
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	
	private String DRIVER_CLASS = "org.gjt.mm.mysql.Driver";
	private String DB_NAME = "cedar_db";
	private String DB_URL = "jdbc:mysql://localhost/"+DB_NAME+"?useUnicode=true&characterEncoding=utf-8";
	private String USER = "root";
	private String PWD = "";
	
	/**
	 * 注册驱动，创建连接。注：执行完SQL操作之后，调用close()方法关闭连接
	 */
	public DBUtil(){
		try {
			Class.forName(DRIVER_CLASS);
			conn = DriverManager.getConnection(DB_URL,USER,PWD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public DBUtil(String dbName, String user, String pwd){
		String dbUrl = "jdbc:mysql://localhost/"+dbName+"?useUnicode=true&characterEncoding=utf-8";
		try {
			Class.forName(DRIVER_CLASS);
			conn = DriverManager.getConnection(dbUrl,user,pwd);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		DBUtil db = new DBUtil();//初始化
		
		//ResultSet rs  = db.executeQuery("select * from FILE_SUBS_INFO where FILE_NAME=?","ff");
		/*ResultSet rs = db.executeQuery("select id,field1 from tb_test");
		Map map = new HashMap();
		if(rs != null){
			try {
				while(rs.next()){
					//System.out.println(rs.getString("DEST_SYS_ID")+" "+rs.getString("SYS_NAME"));
					map.put(rs.getString("id"), rs.getString("field1"));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println(map.size());*/
		
		/*ResultSet rs = db.executeQuery("select * from tb_test");
		ResultSetMetaData rsmd = null;
		try {
			rsmd = rs.getMetaData();
			if(rsmd != null){
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {//i=1(字段位置从1开始)
					System.out.println(rsmd.getColumnName(i));
				}
			}
		} catch (SQLException e) {
			db.close();//关闭连接
			e.printStackTrace();
		} */
		
		System.out.println(db.getFieldsNamesAndType("tb_test"));
		db.close();//关闭连接
	}
	
	/**
	 * 获取表的字段名
	 * @param tbName
	 * @return
	 */
	public String[] getFieldsNames(String tbName){
		boolean tbExist = false;
		executeQuery("SHOW TABLES");
		try {
			while(rs.next()){
				if(rs.getString(1).equals(tbName)){
					tbExist = true;
					break;
				}
			}
			if(tbExist == true){
				executeQuery("SELECT * FROM " + tbName + " LIMIT 1");
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				String[] columnNames = new String[columnCount];
				for (int i = 1; i <= columnCount; i++) {//i=1(字段位置从1开始)
					//System.out.println(rsmd.getColumnName(i));
					columnNames[i-1] = rsmd.getColumnName(i);
					//System.out.println(rsmd.getColumnName(i) + "-" + rsmd.getColumnType(i) + "-" + rsmd.getColumnTypeName(i) +"-"+rsmd.getScale(i));
				}
				return columnNames;
			}else{
				System.out.println("表“"+tbName+"”不存在！");
			}
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 获取表的字段名及字段类型
	 * @param tbName
	 * @return
	 */
	public LinkedHashMap<String,String> getFieldsNamesAndType(String tbName){
		boolean tbExist = false;
		executeQuery("SHOW TABLES");
		try {
			while(rs.next()){
				if(rs.getString(1).equals(tbName)){
					tbExist = true;
					break;
				}
			}
			if(tbExist == true){
				executeQuery("SELECT * FROM " + tbName + " LIMIT 1");
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				LinkedHashMap<String,String> map = new LinkedHashMap<String, String>();
				for (int i = 1; i <= columnCount; i++) {//i=1(字段位置从1开始)
					map.put(rsmd.getColumnName(i), rsmd.getColumnTypeName(i));
				}
				return map;
			}else{
				System.out.println("表“"+tbName+"”不存在！");
			}
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet executeQuery(String sql){
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			close();
			e.printStackTrace();
		}
		return rs;
	}
	
	public ResultSet executeQuery(String sql,String param){
		try {
			pstmt = conn.prepareStatement(sql);
			setParameter(pstmt, param);
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			close();
			e.printStackTrace();
			System.out.println(getParamSql(sql, param));
		}
		return rs;
	}
	
	public ResultSet executeQuery(String sql,String[] params){
		try {
			pstmt = conn.prepareStatement(sql);
			setParameters(pstmt, params);
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			close();
			e.printStackTrace();
			System.out.println(getParamSql(sql, params));
		}
		return rs;
	}
	
	/**
	 * 关闭连接
	 */
	public void close(){
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			pstmt = null;
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;	
		}
	}
	
	/**
	 * 用参数替换掉sql语句中的“?”，返回实际执行的sql语句
	 * @param sql
	 * @param params
	 * @return
	 */
	private static String getParamSql(String sql,Object[] params){
		StringBuffer sbSql = new StringBuffer(sql);
		int index = 0,i = 0;
		while(index >= 0 && i < params.length){
			sbSql.replace(index, index + 1, "'" + (params[i] == null ? "" : params[i].toString()) + "'");
			index = sbSql.indexOf("?");
			i++;
		}
		return sbSql.toString();
	}
	/**
	 * 用参数替换掉sql语句中的“?”，返回实际执行的sql语句
	 * @param sql
	 * @param params
	 * @return
	 */
	private static String getParamSql(String sql,Object param){
		StringBuffer sbSql = new StringBuffer(sql);
		int index = 0;
		sbSql.replace(index, index + 1, "'" + (param == null ? "" : param.toString()) + "'");
		index = sbSql.indexOf("?");
		return sbSql.toString();
	}

	private void setParameters(PreparedStatement pstmt, Object[] params) throws SQLException {
		for (int i = 0; i < params.length; i++) {
			int parameterIndex = i + 1;
			Object param = params[i];
			if (param == null) {
				pstmt.setNull(parameterIndex, Types.NULL);
			} else if (param instanceof Integer) {
				pstmt.setInt(parameterIndex, ((Integer) param).intValue());
			} else if (param instanceof Short) {
				pstmt.setShort(parameterIndex, ((Short) param).shortValue());
			} else if (param instanceof BigDecimal) {
				pstmt.setBigDecimal(parameterIndex, (BigDecimal) param);
			} else if (param instanceof Double) {
				pstmt.setDouble(parameterIndex, ((Double) param).doubleValue());
			} else if (param instanceof Float) {
				pstmt.setFloat(parameterIndex, ((Float) param).floatValue());
			} else if (param instanceof String) {
				pstmt.setString(parameterIndex, (String) param);
			} else if (param instanceof Character) {
				pstmt.setString(parameterIndex, String.valueOf(param));
			} else if (param instanceof Boolean) {
				pstmt.setBoolean(parameterIndex, ((Boolean) param).booleanValue());
			} else if (param instanceof java.sql.Date) {
				pstmt.setDate(parameterIndex, (java.sql.Date) param);
			} else if (param instanceof java.sql.Time) {
				pstmt.setTime(parameterIndex, (java.sql.Time) param);
			} else if (param instanceof java.sql.Timestamp) {
				pstmt.setTimestamp(parameterIndex, (java.sql.Timestamp) param);
			} else if (param instanceof java.util.Date) {
				long time = ((java.util.Date) param).getTime();
				pstmt.setDate(parameterIndex, new java.sql.Date(time));
			} else if (param instanceof Clob) {
				pstmt.setClob(parameterIndex, (Clob) param);
			} else if (param instanceof Blob) {
				pstmt.setBlob(parameterIndex, (Blob) param);
			} else if (param instanceof Long) {
				pstmt.setLong(parameterIndex, ((Integer) param).longValue());
			} else if (param instanceof byte[]) {
				pstmt.setBytes(parameterIndex, (byte[]) param);
			} else if (param instanceof Byte) {
				pstmt.setByte(parameterIndex, ((Byte) param).byteValue());
			} else if (param instanceof java.sql.Array) {
				pstmt.setArray(parameterIndex, (java.sql.Array) param);
			} else {
				throw new IllegalArgumentException("unsupported parameter type: " + param.getClass());
			}
		}
	}
	
	private void setParameter(PreparedStatement pstmt, Object param) throws SQLException {
		if (param == null) {
			pstmt.setNull(1, Types.NULL);
		} else if (param instanceof Integer) {
			pstmt.setInt(1, ((Integer) param).intValue());
		} else if (param instanceof Short) {
			pstmt.setShort(1, ((Short) param).shortValue());
		} else if (param instanceof BigDecimal) {
			pstmt.setBigDecimal(1, (BigDecimal) param);
		} else if (param instanceof Double) {
			pstmt.setDouble(1, ((Double) param).doubleValue());
		} else if (param instanceof Float) {
			pstmt.setFloat(1, ((Float) param).floatValue());
		} else if (param instanceof String) {
			pstmt.setString(1, (String) param);
		} else if (param instanceof Character) {
			pstmt.setString(1, String.valueOf(param));
		} else if (param instanceof Boolean) {
			pstmt.setBoolean(1, ((Boolean) param).booleanValue());
		} else if (param instanceof java.sql.Date) {
			pstmt.setDate(1, (java.sql.Date) param);
		} else if (param instanceof java.sql.Time) {
			pstmt.setTime(1, (java.sql.Time) param);
		} else if (param instanceof java.sql.Timestamp) {
			pstmt.setTimestamp(1, (java.sql.Timestamp) param);
		} else if (param instanceof java.util.Date) {
			long time = ((java.util.Date) param).getTime();
			pstmt.setDate(1, new java.sql.Date(time));
		} else if (param instanceof Clob) {
			pstmt.setClob(1, (Clob) param);
		} else if (param instanceof Blob) {
			pstmt.setBlob(1, (Blob) param);
		} else if (param instanceof Long) {
			pstmt.setLong(1, ((Integer) param).longValue());
		} else if (param instanceof byte[]) {
			pstmt.setBytes(1, (byte[]) param);
		} else if (param instanceof Byte) {
			pstmt.setByte(1, ((Byte) param).byteValue());
		} else if (param instanceof java.sql.Array) {
			pstmt.setArray(1, (java.sql.Array) param);
		} else {
			throw new IllegalArgumentException("unsupported parameter type: " + param.getClass());
		}
	}




}
 